package com.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bean.ColumnBean;
import com.bean.TableBean;

public class OracleUtil {  
    public static Connection getConnection() { 
    	try {
			return JdbcUtil.getConnection("oracle.properties");
		} catch (Exception e) {
			e.printStackTrace();
		}
    	
    	return null;
    }  
    
    public static void releaseDB(PreparedStatement ps) {  
    	JdbcUtil.releaseDB(ps);
    }
    
    public static void releaseDB(ResultSet resultSet, Statement statement, Connection connection) { 
    	JdbcUtil.releaseDB(resultSet, statement, connection);
    }
    
    /** 
     * 获取当前数据库下的所有表名称 和表注释
     * @return 
     * @throws Exception 
     */  
	public static List<TableBean> getAllTableName() throws Exception {
		List<TableBean> tableList = new ArrayList<TableBean>();
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		TableBean tableBean = null;
		
		String sql = "select a.TABLE_NAME,b.COMMENTS from user_tables a,user_tab_comments b WHERE a.TABLE_NAME=b.TABLE_NAME order by TABLE_NAME";
		
		try {
			conn = OracleUtil.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				tableBean = new TableBean(rs.getString(1), rs.getString(2));
				tableList.add(tableBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleUtil.releaseDB(rs, stmt, conn);
		}
		
		return tableList;
	}
	
    /**
     * 获取表列信息
     * 
     * @param tableName
     * @return
     */
    public static List<ColumnBean> getColList(String tableName) {
        ArrayList<ColumnBean> colList = new ArrayList<ColumnBean>();
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		ColumnBean columnBean = null;
		
		String sql="select t1.COLUMN_NAME,t1.DATA_TYPE ,t1.DATA_LENGTH,t1.DATA_PRECISION,t1.DATA_SCALE,t1.NULLABLE,t2.comments,t5.column_name primaryKey"
        + " from all_tab_columns t1 left join user_col_comments t2 on t1.TABLE_NAME=t2.table_name and t1.COLUMN_NAME=t2.COLUMN_NAME "
        + " left join (select t3.table_name,t3.column_name from user_cons_columns t3, user_constraints t4 "
        + " where t3.constraint_name = t4.constraint_name and t4.constraint_type = 'P') t5 on t1.TABLE_NAME=t5.table_name and t1.COLUMN_NAME=t5.column_name"
        + " where t1.table_name='"+tableName+"'";
        
		try {
			conn = OracleUtil.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery(sql);
			int dataLength = 0;
			int dataPrecision = 0;
			int dataScale = 0;
			while (rs.next()) {
				try {
					dataLength = Integer.parseInt(rs.getString(3));
				} catch (NumberFormatException e) {
					dataLength = 0;
				}
				try {
					dataPrecision = Integer.parseInt(rs.getString(4));
				} catch (NumberFormatException e) {
					dataPrecision = 0;
				}
				try {
					dataScale = Integer.parseInt(rs.getString(5));
				} catch (NumberFormatException e) {
					dataScale = 0;
				}
				columnBean = new ColumnBean(rs.getString(1), rs.getString(2), dataLength, dataPrecision, dataScale, rs.getString(6), rs.getString(7), rs.getString(8));
				colList.add(columnBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleUtil.releaseDB(rs, stmt, conn);
		}
		
		return colList;
    }
    
    /**
     * 
     * 获取oracle表数据
     * @param tableName
     * @return
     */
    public static ResultSet getDataFromTable(String tableName) {
        Connection oConn = OracleUtil.getConnection();
        PreparedStatement pstm = null;

        try {
        	String sql = "select * from " + tableName;
            pstm = oConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            return pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
} 